package com.totoro.tyrank.sql;

import com.totoro.tyrank.TyRank;
import com.totoro.tyrank.annotation.TableField;
import com.totoro.tyrank.constants.StaticSystemVarConst;
import com.totoro.tyrank.utils.ColorUtil;
import com.totoro.tyrank.utils.DateUtil;
import com.totoro.tyrank.utils.StrUtil;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.lang.reflect.Field;
import java.sql.*;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 数据库操作基类，优化线程安全与资源管理
 */
public class BaseDao {
	private final HikariDataSource dataSource;

	// 缓存实体类字段与数据库列的映射关系，提升反射效率
	private final Map<Class<?>, Map<String, Field>> fieldCache = new HashMap<>();

	public BaseDao(HikariConfig hikariConfig) {
		this.dataSource = new HikariDataSource(hikariConfig);
	}

	/**
	 * 初始化数据表
	 */
	public void initTables() {
		String createPlayerDataTableSQL = "CREATE TABLE IF NOT EXISTS ty_rank_player_data (" +
				"player_identity VARCHAR(255) NOT NULL, " +
				"rank_id VARCHAR(255) NOT NULL, " +
				"ranking INT NOT NULL, " +
				"current_value VARCHAR(255) NOT NULL, " +
				"reward_receive VARCHAR(20) NOT NULL, " +
				"update_date_time VARCHAR(255) NOT NULL)";
		String createRankDataTableSQL = "CREATE TABLE IF NOT EXISTS ty_rank_data (" +
				"rank_id VARCHAR(255) NOT NULL, " +
				"is_end VARCHAR(20) NOT NULL, " +
				"update_date_time VARCHAR(255) NOT NULL)";

		try (Connection con = getConnection();
			 Statement statement = con.createStatement()) {

			statement.execute(createPlayerDataTableSQL);
			statement.execute(createRankDataTableSQL);
			TyRank.getInstance().getServer().getConsoleSender()
					.sendMessage(StaticSystemVarConst.CONSOLE_PREFIX + ColorUtil.color("&c成功创建数据表: &e[ty_rank_player_data]"));
			TyRank.getInstance().getServer().getConsoleSender()
					.sendMessage(StaticSystemVarConst.CONSOLE_PREFIX + ColorUtil.color("&c成功创建数据表: &e[ty_rank_data]"));

		} catch (SQLException e) {
			throw new RuntimeException("创建数据表失败", e);
		}
	}

	/**
	 * 获取数据库连接
	 */
	private Connection getConnection() throws SQLException {
		if (dataSource.isClosed()) {
			throw new SQLException("数据源已关闭");
		}
		return dataSource.getConnection();
	}

	/**
	 * 公共查询方法（支持实体类映射）
	 */
	public <T> List<T> baseQuery(String sql, Class<T> entityClass, Object... params) {
		List<T> resultList = new ArrayList<>();

		try (Connection con = getConnection();
			 PreparedStatement st = con.prepareStatement(sql)) {

			// 绑定参数
			bindParams(st, params);

			try (ResultSet rs = st.executeQuery()) {
				ResultSetMetaData metaData = rs.getMetaData();
				// 获取字段映射缓存（首次加载后缓存，提升效率）
				Map<String, Field> columnFieldMap = getColumnFieldMap(entityClass, metaData);

				while (rs.next()) {
					T entity = entityClass.newInstance();
					// 循环列名匹配字段并赋值
					for (int i = 1; i <= metaData.getColumnCount(); i++) {
						String columnName = metaData.getColumnName(i);
						Field field = columnFieldMap.get(columnName);
						if (field == null) {
							continue; // 跳过未映射的字段
						}

						// 安全设置字段值（处理访问权限）
						setFieldValue(entity, field, rs, columnName);
					}
					resultList.add(entity);
				}
			}

		} catch (Exception e) {
			throw new RuntimeException("查询数据失败", e);
		}

		return resultList;
	}

	/**
	 * 单列查询（适用于COUNT、SUM等单值查询）
	 */
	public <T> List<T> singleQuery(String sql, Class<T> resultType, Object... params) {
		List<T> resultList = new ArrayList<>();

		try (Connection con = getConnection();
			 PreparedStatement st = con.prepareStatement(sql)) {

			bindParams(st, params);

			try (ResultSet rs = st.executeQuery()) {
				while (rs.next()) {
					Object value = rs.getObject(1);
					if (value == null) {
						resultList.add(null);
						continue;
					}
					// 安全类型转换
					T convertedValue = convertValue(value, resultType);
					resultList.add(convertedValue);
				}
			}

		} catch (Exception e) {
			throw new RuntimeException("单列查询失败", e);
		}

		return resultList;
	}

	/**
	 * 增删改通用方法
	 */
	public int executeUpdate(String sql, Object... params) {
		try (Connection con = getConnection();
			 PreparedStatement st = con.prepareStatement(sql)) {

			bindParams(st, params);
			return st.executeUpdate();

		} catch (SQLException e) {
			throw new RuntimeException("执行更新失败", e);
		}
	}

	/**
	 * 绑定SQL参数
	 */
	private void bindParams(PreparedStatement st, Object... params) throws SQLException {
		if (params == null || params.length == 0) {
			return;
		}
		for (int i = 0; i < params.length; i++) {
			Object param = params[i] == null ? null : params[i];
			st.setObject(i + 1, param);
		}
	}

	/**
	 * 获取实体类字段与数据库列的映射关系（缓存优化）
	 */
	private Map<String, Field> getColumnFieldMap(Class<?> entityClass, ResultSetMetaData metaData) throws SQLException {
		if (fieldCache.containsKey(entityClass)) {
			return fieldCache.get(entityClass);
		}

		Map<String, Field> columnFieldMap = new HashMap<>();
		Field[] fields = entityClass.getDeclaredFields();

		for (Field field : fields) {
			TableField tableField = field.getAnnotation(TableField.class);
			String columnName;

			if (tableField != null && !tableField.value().isEmpty()) {
				columnName = tableField.value();
			} else {
				// 驼峰转下划线（如userName -> user_name）
				columnName = StrUtil.upperCharToUnderLine(field.getName());
			}

			// 验证列是否存在于结果集中
			boolean columnExists = false;
			for (int i = 1; i <= metaData.getColumnCount(); i++) {
				if (metaData.getColumnName(i).equals(columnName)) {
					columnExists = true;
					break;
				}
			}
			if (columnExists) {
				columnFieldMap.put(columnName, field);
			}
		}

		fieldCache.put(entityClass, columnFieldMap);
		return columnFieldMap;
	}

	/**
	 * 给实体类字段设置值（处理权限和类型转换）
	 */
	private void setFieldValue(Object entity, Field field, ResultSet rs, String columnName) throws Exception {
		TableField tableField = field.getAnnotation(TableField.class);
		Class<?> targetType = tableField != null ? tableField.mappingType() : field.getType();
		Object value = rs.getObject(columnName);

		// 处理null值
		if (value == null) {
			setFieldSafely(entity, field, null);
			return;
		}

		// 类型转换
		Object convertedValue = convertValue(value, targetType);
		setFieldSafely(entity, field, convertedValue);
	}

	/**
	 * 安全设置字段值
	 */
	private void setFieldSafely(Object entity, Field field, Object value) throws IllegalAccessException {
		field.setAccessible(true);
		field.set(entity, value);
	}

	/**
	 * 通用类型转换（支持常见类型）
	 */
	@SuppressWarnings("unchecked")
	private <T> T convertValue(Object value, Class<T> targetType) {
		if (targetType.isInstance(value)) {
			return (T) value;
		}

		// 字符串转基础类型
		if (value instanceof String) {
			String strValue = (String) value;
			if (targetType == Integer.class || targetType == int.class) {
				return (T) Integer.valueOf(strValue);
			} else if (targetType == Long.class || targetType == long.class) {
				return (T) Long.valueOf(strValue);
			} else if (targetType == Double.class || targetType == double.class) {
				return (T) Double.valueOf(strValue);
			} else if (targetType == Boolean.class || targetType == boolean.class) {
				return (T) Boolean.valueOf(strValue);
			} else if (targetType == LocalDateTime.class) {
				return (T) DateUtil.strFormatDate(strValue);
			}
		}

		// 数字类型互转
		if (value instanceof Number) {
			Number numValue = (Number) value;
			if (targetType == Integer.class || targetType == int.class) {
				return (T) Integer.valueOf(numValue.intValue());
			} else if (targetType == Long.class || targetType == long.class) {
				return (T) Long.valueOf(numValue.longValue());
			} else if (targetType == Double.class || targetType == double.class) {
				return (T) Double.valueOf(numValue.doubleValue());
			}
		}

		// 无法转换时返回原始值（可能抛出ClassCastException）
		return (T) value;
	}

	/**
	 * 关闭数据源（程序退出时调用）
	 */
	public void closeDataSource() {
		if (!dataSource.isClosed()) {
			dataSource.close();
		}
	}
}
